from __future__ import with_statement
'''from sqlite3 import dbapi2 as sqlite3'''
import oursql
from contextlib import closing
from flask import Flask, request, session, g, redirect, url_for, abort, \
     render_template, flash
	 
DEBUG = True

app = Flask(__name__)
app.config.from_object(__name__)


def connect_db():
    return oursql.connect (host = "localhost", user = "root", passwd = "root", db = "bridgeit")

@app.before_request
def before_request():
    g.db = connect_db()

@app.after_request
def close_connection(response):
    g.db.close()
    return response

@app.route('/')	
def search():
    conn = oursql.connect (host = "localhost", user = "root", passwd = "root", db = "bridgeit")
    cursor = conn.cursor()
    cursor.execute('select id from BridgeIt.country')
    from_Country = cursor.fetchall()
    print from_Country
    cursor.close()
    return render_template('Search.html',from_Country=from_Country)


@app.route('/search_button_pressed' , methods=['GET' , 'POST'])
def search_button_pressed():
    containTextField = request.form['containTextField']
    fromList = request.form['fromList']
    toList = request.form['toList']
    print('asdasd')
    cursor = g.db.cursor()
    cursor.execute("select id from BridgeIt.Country where name='"+fromList+"'")
    from_Country = [dict(fromId=row1[0])for row1 in cursor.fetchall()]
    ins = from_Country[0]
    cursor.close()
    cursor = g.db.cursor()
    cursor.execute("select id from BridgeIt.Country where name='"+toList+"'")
    to_Country = [dict(toId=row2[0])for row2 in cursor.fetchall()]
    cursor.close()
    cursor = g.db.cursor()
    print(ins.fromId)
    cursor.execute("select * from BridgeIt.Bridge where from_country_id="+from_Country[0].fromId+" AND to_country_id="+to_Country[0].toId+" AND text LIKE '%"+containTextField+"%'")
    searchResults = [dict(datetime=row3[0], 
                        text=row3[1],
                        fromcountry=row3[2],
                        tocountry=row3[3],
                        photourl=row3[4],
                        videourl=row3[5],
                        likes=row3[6],
                        dislikes=row3[7]) for row3 in cursor.fetchall()]
    cursor.close()
    return render_template('search_button_pressed.html',searchResults=searchResults)



if __name__ == '__main__':
    app.run()
